package com.ylwl.datacenterdemo.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.ylwl.datacenterdemo.annotation.TimeConsuming;
import com.ylwl.datacenterdemo.entity.ExcelHeader;
import com.ylwl.datacenterdemo.entity.ExcelImportConfig;
import com.ylwl.datacenterdemo.entity.ExcelRow;
import com.ylwl.datacenterdemo.entity.TableMetadata;
import com.ylwl.datacenterdemo.mapper.DynamicDataMapper;
import com.ylwl.datacenterdemo.service.impl.DefaultFieldNameGenerator;
import com.ylwl.datacenterdemo.service.impl.DatabaseTableStructureManager;
import com.ylwl.datacenterdemo.util.TableNameConverter;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.*;

@Slf4j
@Service
@RequiredArgsConstructor
public class ExcelImportService {

    private final DynamicDataMapper dynamicDataMapper;
    private final DatabaseTableStructureManager tableStructureManager;
    private final DefaultFieldNameGenerator fieldNameGenerator;
    private final TableNameConverter tableNameConverter;

    /**
     * 从本地文件导入Excel数据到数据库
     * @param filePath Excel文件路径
     * @param tableName 可选的表名，如果为null则使用文件名
     */
    @TimeConsuming("Excel数据导入")
    public void importExcelToDatabase(String filePath, String tableName) {
        File file = new File(filePath);
        String fileName = file.getName();

        // 如果用户未指定表名，则根据文件名生成
        String finalTableName = tableName != null ?
                tableNameConverter.convertToTableName(tableName) :
                tableNameConverter.convertToTableName(fileName);

        log.info("开始导入Excel文件: {}，目标表名: {}", fileName, finalTableName);

        // 使用监听器模式读取Excel数据并导入数据库
        ExcelDataListener listener = new ExcelDataListener(finalTableName, dynamicDataMapper, tableStructureManager, fieldNameGenerator);
        // 明确指定从第一行开始读取
        EasyExcel.read(file, listener).sheet(0).headRowNumber(0).doRead();
        
        log.info("Excel数据导入完成，总共处理 {} 行数据", listener.getTotalRows());
    }

    /**
     * 通过文件流导入Excel数据到数据库
     * @param file Excel文件流
     * @param tableName 可选的表名，如果为null则使用文件名
     */
    @TimeConsuming("Excel数据流导入")
    public void importExcelByStream(MultipartFile file, String tableName) throws IOException {
        String fileName = file.getOriginalFilename();

        // 如果用户未指定表名，则根据文件名生成
        String finalTableName = tableName != null ?
                tableNameConverter.convertToTableName(tableName) :
                tableNameConverter.convertToTableName(fileName);

        log.info("开始导入Excel文件: {}，目标表名: {}", fileName, finalTableName);

        // 使用监听器模式读取Excel数据并导入数据库
        ExcelDataListener listener = new ExcelDataListener(finalTableName, dynamicDataMapper, tableStructureManager, fieldNameGenerator);
        // 明确指定从第一行开始读取
        EasyExcel.read(file.getInputStream(), listener).sheet(0).headRowNumber(0).doRead();
        
        log.info("Excel数据导入完成，总共处理 {} 行数据", listener.getTotalRows());
    }

    // 保持原有方法以确保向后兼容
    public void importExcelToDatabase(String filePath) {
        this.importExcelToDatabase(filePath, null);
    }

    // 保持原有方法以确保向后兼容
    public void importExcelByStream(MultipartFile file) throws IOException {
        this.importExcelByStream(file, null);
    }
    
    /**
     * Excel数据读取监听器
     */
    public static class ExcelDataListener implements ReadListener<Map<Integer, String>> {
        private final ExcelImportConfig config = new ExcelImportConfig();
        
        private final String tableName;
        private final DynamicDataMapper dynamicDataMapper;
        private final DatabaseTableStructureManager tableStructureManager;
        private final DefaultFieldNameGenerator fieldNameGenerator;
        
        private List<ExcelRow> cachedData = new ArrayList<>();
        private List<ExcelHeader> headers = new ArrayList<>();
        private TableMetadata tableMetadata;
        private boolean headCaptured = false;
        private int totalRows = 0;
        private int currentRowNumber = 1; // 记录当前处理的Excel行号
        private ExecutorService executorService;

        public ExcelDataListener(String tableName, 
                                 DynamicDataMapper dynamicDataMapper, 
                                 DatabaseTableStructureManager tableStructureManager,
                                 DefaultFieldNameGenerator fieldNameGenerator) {
            this.tableName = tableName;
            this.dynamicDataMapper = dynamicDataMapper;
            this.tableStructureManager = tableStructureManager;
            this.fieldNameGenerator = fieldNameGenerator;
            this.executorService = Executors.newFixedThreadPool(config.getThreadPoolSize());
        }

        @Override
        public void invoke(Map<Integer, String> rowData, AnalysisContext context) {
            log.debug("读取第 {} 行数据: {}", currentRowNumber, rowData);
            
            if (!headCaptured) {
                // 第一行是表头
                processHeaderRow(rowData);
                headCaptured = true;
                currentRowNumber++; // 表头行处理完毕，行号递增
                return;
            }
            
            log.debug("处理数据行（第 {} 行）: {}", currentRowNumber, rowData);
            
            // 添加行号信息到数据中
            ExcelRow excelRow = new ExcelRow(currentRowNumber, new LinkedHashMap<>(rowData));
            
            cachedData.add(excelRow);
            totalRows++;
            currentRowNumber++;
            
            // 达到批次大小，批量插入
            if (cachedData.size() >= config.getBatchSize()) {
                saveData();
            }
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // 处理剩余数据
            if (!cachedData.isEmpty()) {
                saveData();
            }
            // 关闭线程池
            executorService.shutdown();
        }
        
        /**
         * 处理表头行
         * @param rowData 表头行数据
         */
        private void processHeaderRow(Map<Integer, String> rowData) {
            log.info("检测到表头行（第 {} 行）: {}", currentRowNumber, rowData);
            
            // 创建表头对象列表
            for (Map.Entry<Integer, String> entry : rowData.entrySet()) {
                ExcelHeader header = new ExcelHeader(entry.getKey(), entry.getValue());
                headers.add(header);
            }
            
            log.info("表头内容: {}", rowData.values());
            
            // 创建表元数据
            tableMetadata = new TableMetadata(tableName);
            tableMetadata.setHeaders(headers);
            
            // 生成字段名映射
            Map<Integer, String> fieldMap = fieldNameGenerator.generateFieldNames(headers);
            tableMetadata.setColumnIndexToFieldMap(fieldMap);
            
            // 创建表结构
            tableStructureManager.createTableStructure(tableMetadata);
        }

        /**
         * 保存数据到数据库
         */
        private void saveData() {
            if (tableMetadata == null) {
                log.error("表元数据未初始化，无法保存数据");
                return;
            }
            
            if (tableMetadata.getColumnIndexToFieldMap() == null || tableMetadata.getColumnIndexToFieldMap().isEmpty()) {
                log.warn("有效表头为空，无法保存数据");
                cachedData.clear();
                return;
            }
            
            log.info("正在保存 {} 行数据到表 {}", cachedData.size(), tableName);
            
            try {
                // 将数据分批处理，使用多线程提高效率
                List<Future<?>> futures = new ArrayList<>();
                
                // 按照较小的批次分组，以便更好地利用多线程
                int threadBatchSize = Math.max(500, config.getBatchSize() / config.getThreadPoolSize());
                
                for (int i = 0; i < cachedData.size(); i += threadBatchSize) {
                    int endIndex = Math.min(i + threadBatchSize, cachedData.size());
                    List<ExcelRow> batchData = new ArrayList<>(cachedData.subList(i, endIndex));
                    
                    // 提交任务到线程池
                    Future<?> future = executorService.submit(() -> {
                        processBatchData(batchData);
                    });
                    futures.add(future);
                }
                
                // 等待所有任务完成
                for (Future<?> future : futures) {
                    try {
                        future.get(config.getThreadTimeoutSeconds(), TimeUnit.SECONDS); // 设置超时时间
                    } catch (ExecutionException e) {
                        log.error("处理数据批次时出错: ", e.getCause());
                    } catch (InterruptedException e) {
                        log.error("处理数据批次时被中断: ", e);
                        Thread.currentThread().interrupt(); // 恢复中断状态
                    } catch (TimeoutException e) {
                        log.error("处理数据批次时超时: ", e);
                    }
                }
                
                log.info("成功保存 {} 行数据", cachedData.size());
            } finally {
                cachedData.clear();
            }
        }
        
        /**
         * 处理一批数据
         * @param batchData 一批数据
         */
        private void processBatchData(List<ExcelRow> batchData) {
            try {
                // 逐条处理数据
                for (ExcelRow excelRow : batchData) {
                    // 转换为键值对形式，只处理有效的表头列
                    Map<String, Object> formattedData = new LinkedHashMap<>();
                    
                    // 添加行号字段
                    formattedData.put("row_index", excelRow.getRowNumber());
                    
                    // 处理其他字段
                    Map<Integer, String> fieldMap = tableMetadata.getColumnIndexToFieldMap();
                    for (Map.Entry<Integer, String> entry : fieldMap.entrySet()) {
                        Integer columnIndex = entry.getKey();
                        String fieldName = entry.getValue();
                        
                        // 获取该列的数据，如果无数据则为null
                        String cellValue = excelRow.getData().get(columnIndex);
                        
                        // 使用字段名
                        formattedData.put(fieldName, cellValue);
                    }
                    
                    // 保存到数据库
                    dynamicDataMapper.insertDynamicData(tableName, formattedData);
                }
            } catch (Exception e) {
                log.error("处理数据批次时出错: ", e);
            }
        }
        
        public int getTotalRows() {
            return totalRows;
        }
    }
}